﻿using Hxj.Data;
using RecruitStudents.Utility.Helper;
using RecruitStudents.Utility.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace RecruitStudents.Client.DAL
{
    public class DALScore : Base.BaseService
    {
        public DataTable GetScoreQueryUp(string strExamRoomID, string strTeacherID, string strExaminationID, string strSFYSB,
            string strExamPlaceID, string strSpecialityKindsNO, string strExamLevel)
        {
            string strSql = @"
                            select 
                                a.SpecialityKindsNO,
                                a.ExamLevel,
                                C.IdentityCard,
                                C.SignUpID,
                                C.NMT_SignUpID,
                                A.StudentScoreListID,
                                A.LotNO,
                                B.ExamRoomName,
                                A.Name AS StudentName,
                                A.AllowExamID,
                                A.Teacher1Name + ':' +  CAST(A.Score1 AS VARCHAR) AS Score1,
                                A.Teacher2Name + ':' +  CAST(A.Score2 AS VARCHAR) AS Score2,
                                A.Teacher3Name + ':' +  CAST(A.Score3 AS VARCHAR) AS Score3,
                                A.Teacher4Name + ':' +  CAST(A.Score4 AS VARCHAR) AS Score4,
                                A.Teacher5Name + ':' +  CAST(A.Score5 AS VARCHAR) AS Score5,
                                A.Teacher6Name + ':' +  CAST(A.Score6 AS VARCHAR) AS Score6,
                                A.Teacher7Name + ':' +  CAST(A.Score7 AS VARCHAR) AS Score7,
                                A.TatolScore,
                                A.Remark,
                                A.CreateTime,
                                '1' as IsUp,
                                A.IsPass,
                                A.IsPrint,    
                                A.IsPublish,                            
                                D.DicName as ExamProvinceName,
                                C.Sex,
                                (case when C.Sex = 2 then '女'
								     when c.Sex = 1   then '男'
								 end ) as SexName,
                                (select COUNT(1) from TB_Pad_TeacherScoreList where AllowExamID = A.AllowExamID  and ExamRoomNO = A.ExamRoomNO) AS iCount                                
                            from TB_Pad_StudentScore_Up A
                            left join TB_Exam_ExamRoom	B ON A.ExamRoomNO = B.ExamRoomNO
                            left join TB_SignUpStudents C ON a.NMT_SignUpID=c.NMT_SignUpID and A.IdentityCard=C.IdentityCard
                            left join TB_Dic_ExamProvince D on C.ExamProvince=D.DicNO
                            where A.ExamPlaceNO='{0}' and A.ExamLevel='{1}' and A.SpecialityKindsNO like '{2}%' {3} {4}
                            order by A.TatolScore desc 
                        ";
            string strExaminationIDCondition = string.Empty;
            if (!string.IsNullOrEmpty(strExaminationID))
            {
                strExaminationIDCondition = "and B.EntretenimientohID ='" + strExaminationID + "'";
            }
            string strCondition = "";
            if (strExamRoomID != "")
            {
                strCondition += " and B.ExamRoomNO = '" + strExamRoomID + "' ";
            }
            if (strTeacherID != "")
            {
                strCondition += " and (A.Teacher1 = '" + strTeacherID + "' or A.Teacher2 = '" + strTeacherID + "' or A.Teacher3 = '" + strTeacherID + "' or A.Teacher4 = '" + strTeacherID + "' or A.Teacher5 = '" + strTeacherID + "' or A.Teacher6 = '" + strTeacherID + "' or A.Teacher7 = '" + strTeacherID + "') ";
            }
            strSql = string.Format(strSql, strExamPlaceID, strExamLevel, strSpecialityKindsNO, strExaminationIDCondition, strCondition);
            return GetTable(strSql);

        }
        public DataTable GetScoreQuery(string strExamRoomID, string strTeacherID, string strExaminationID, string strSFYSB, DataTable dtTeacher,
            string strExamPlaceID, string strSpecialityKindsNO, string strExamLevel)
        {
            string strStudentScoreWhere = string.Empty;
            if (strSFYSB == "1" && dtTeacher != null)
            {
                foreach (DataRow dr in dtTeacher.Rows)
                {
                    string xh = dr["XH"].ToString();
                    if (strStudentScoreWhere.Length == 0)
                    {
                        strStudentScoreWhere = " and ISNULL(Teacher" + xh + ",'')!=''";
                    }
                    else
                    {
                        strStudentScoreWhere += " and ISNULL(Teacher" + xh + ",'')!=''";
                    }
                }
            }
            string strSql = @"
                            select 
                                a.SpecialityKindsNO,
                                a.ExamLevel,
                                C.IdentityCard,
                                C.NMT_SignUpID,
                                A.StudentScoreListID,
                                A.LotNO,
                                B.ExamRoomName,
                                A.Name AS StudentName,
                                A.AllowExamID,
                                A.Teacher1Name + ':' +  CAST(A.Score1 AS VARCHAR) AS Score1,
                                A.Teacher2Name + ':' +  CAST(A.Score2 AS VARCHAR) AS Score2,
                                A.Teacher3Name + ':' +  CAST(A.Score3 AS VARCHAR) AS Score3,
                                A.Teacher4Name + ':' +  CAST(A.Score4 AS VARCHAR) AS Score4,
                                A.Teacher5Name + ':' +  CAST(A.Score5 AS VARCHAR) AS Score5,
                                A.Teacher6Name + ':' +  CAST(A.Score6 AS VARCHAR) AS Score6,
                                A.Teacher7Name + ':' +  CAST(A.Score7 AS VARCHAR) AS Score7,
                                A.TatolScore,
                                A.Remark,
                                A.CreateTime,
                                A.IsUp,
                                A.IsPass,
                                A.IsPrint,   
                                A.IsPublish,                               
                                D.DicName as ExamProvinceName,
                                C.Sex,
                                (case when C.Sex = 2 then '女'
								     when c.Sex = 1   then '男'
								 end ) as SexName,
                                (select COUNT(1) from TB_Pad_TeacherScoreList where AllowExamID = A.AllowExamID  and ExamRoomNO = A.ExamRoomNO) AS iCount                                
                            from TB_Pad_StudentScoreList A
                            left join TB_Exam_ExamRoom	B ON A.ExamRoomNO = B.ExamRoomNO
                            left join TB_SignUpStudents C ON a.NMT_SignUpID=c.NMT_SignUpID and A.IdentityCard=C.IdentityCard
                            left join TB_Dic_ExamProvince D on C.ExamProvince=D.DicNO
                            where A.ExamPlaceNO='{3}' and A.ExamLevel='{5}' and A.SpecialityKindsNO like '{4}%' {1} {2} {0} 
                            order by A.TatolScore desc 
                        ";
            string strCondition = "";
            if (strExamRoomID != "")
            {
                strCondition += " and B.ExamRoomNO = '" + strExamRoomID + "' ";
            }
            string strExaminationIDCondition = string.Empty;
            if (!string.IsNullOrEmpty(strExaminationID))
            {
                strExaminationIDCondition = "and B.EntretenimientohID ='" + strExaminationID + "'";
            }
            if (strTeacherID != "")
            {
                strCondition += " and (A.Teacher1 = '" + strTeacherID + "' or A.Teacher2 = '" + strTeacherID + "' or A.Teacher3 = '" + strTeacherID + "' or A.Teacher4 = '" + strTeacherID + "' or A.Teacher5 = '" + strTeacherID + "' or A.Teacher6 = '" + strTeacherID + "' or A.Teacher7 = '" + strTeacherID + "') ";
            }
            strSql = string.Format(strSql, strCondition, strExaminationIDCondition, strStudentScoreWhere, strExamPlaceID, strSpecialityKindsNO, strExamLevel);
            return base.GetTable(strSql);
        }

        public DataTable GetTeacherScoreList(string strAllowExamID, string strExaminationID, string strSpecialityKindsNO, string strExamLevel)
        {
            if (string.IsNullOrEmpty(strExaminationID))
            {
                string strSql = @"select 
                                A.DisplayName,
                                A.ScoreAll as Score,
                                A.CreateTime,
                                B.ExamRoomName
                            from TB_Pad_TeacherScoreList A
                            left join TB_Exam_ExamRoom	 B ON A.ExamRoomNO = B.ExamRoomNO
                            WHERE 1=1 AND AllowExamID = '{0}' and a.SpecialityKindsNO='{1}' and a.ExamLevel='{2}'
                            ORDER BY A.CreateTime";
                strSql = string.Format(strSql, strAllowExamID, strSpecialityKindsNO, strExamLevel);
                return base.GetTable(strSql);
            }
            else
            {
                string strSql = @"
                            select 
	                            A.DisplayName,
	                            A.ScoreAll as Score,
	                            A.CreateTime,
	                            B.ExamRoomName
                            from TB_Pad_TeacherScoreList A
                            left join TB_Exam_ExamRoom	 B ON A.ExamRoomNO = B.ExamRoomNO
                            WHERE 1=1 AND AllowExamID = '{0}' and B.EntretenimientohID = '{1}'
                            ORDER BY A.CreateTime
                        ";
                strSql = string.Format(strSql, strAllowExamID, strExaminationID);
                return base.GetTable(strSql);
            }
        }

        public string SaveUpData(List<string> listAllowExam, List<string> listIsPass, string strExamLevel, string strExamPlaceID, string strSpecialityKindsNO)
        {
            string strSqlUpdate = string.Empty;
            DbTrans trans = DbHelper.GetDatabase().BeginTransaction();
            try
            {
                string strSql = @"Update TB_Exam_SignUpStudentsSpeciality set IsPass = '0',ExamLevel = '{2}' where  IsActive = '1' and AllowExamID='{0}'
                            Update TB_Pad_StudentScoreList     set IsUp = '1',IsPass='{3}' where AllowExamID = '{0}' and ExamPlaceNO='{1}' and ExamLevel='{2}'                            
                ";//Update TB_Exam_SignUpStudentsSpeciality set ExamLevel='{2}',IsPass='{3}'   where IsActive='1' and AllowExamID = '{0}'
                for (int i = 0; i < listAllowExam.Count; i++)
                {
                    strSqlUpdate = string.Format(strSql, listAllowExam[i], strExamPlaceID, strExamLevel, listIsPass[i]);
                    DbHelper.GetDatabase().FromSql(strSqlUpdate).SetDbTransaction(trans).ExecuteNonQuery();
                }
                trans.Commit();
                return string.Empty;
            }
            catch (Exception ex)
            {
                trans.Rollback();
                return ex.Message;
            }
            finally
            {
                trans.Close();
            }
        }

        public void SavePadStudentScoreUp(List<TB_Pad_StudentScore_Up> listUp, string strSpecialityKindsNO, string strExamLevel, string strExamPlaceID)
        {
            DbTrans trans = DbHelper.GetDatabase().BeginTransaction();
            try
            {
                if (listUp.Count > 0)
                {
                    DbHelper.GetDatabase().FromSql("delete TB_Pad_StudentScore_Up where SpecialityKindsNO='" + strSpecialityKindsNO + "' and ExamPlaceNO='" + strExamPlaceID + "' and ExamLevel='" + strExamLevel + "'").SetDbTransaction(trans).ExecuteNonQuery();
                    foreach (TB_Pad_StudentScore_Up model in listUp)
                    {
                        DbHelper.GetDatabase().Insert<TB_Pad_StudentScore_Up>(model, trans);
                    }
                }
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
            }
            finally
            {
                trans.Close();
            }
        }

        public List<TB_Pad_StudentScoreList> GetPadStudentScoreList(string strSpecialityKindsNO, string strExamLevel, string strExamPlaceID, string strIsPass)
        {
            return base.GetList<TB_Pad_StudentScoreList>(
                TB_Pad_StudentScoreList._.SpecialityKindsNO == strSpecialityKindsNO && TB_Pad_StudentScoreList._.ExamLevel == strExamLevel
                && TB_Pad_StudentScoreList._.ExamPlaceNO == strExamPlaceID && TB_Pad_StudentScoreList._.IsPass == strIsPass)
                .ToList();
        }

        public List<TB_Pad_StudentScore_Up> GetPadStudentScorePublishList(string strSpecialityKindsNO, string strExamLevel, string strExamPlaceID)
        {
            return base.GetList<TB_Pad_StudentScore_Up>(
                TB_Pad_StudentScore_Up._.SpecialityKindsNO == strSpecialityKindsNO && TB_Pad_StudentScore_Up._.ExamLevel == strExamLevel
                && TB_Pad_StudentScore_Up._.ExamPlaceNO == strExamPlaceID && TB_Pad_StudentScore_Up._.IsPass == '1' && TB_Pad_StudentScore_Up._.IsPublish == '1')
                .ToList();
        }

        public string SavePublishData(List<string> listAllowExam, List<string> listIsPass, string strExamLevel, string strExamPlaceID, 
            string strSpecialityKindsNO, string strIsInOut)
        {
            string strSqlUpdate = string.Empty;
            DbTrans trans = DbHelper.GetDatabase().BeginTransaction();
            try
            {
                if (strIsInOut == "1")
                {
                    string strSql = "Update TB_Pad_StudentScore_Up set IsPass='{3}',IsPublish = '1' where AllowExamID = '{0}' and ExamPlaceNO='{1}' and ExamLevel='{2}'";
                    for (int i = 0; i < listAllowExam.Count; i++)
                    {
                        strSqlUpdate = string.Format(strSql, listAllowExam[i], strExamPlaceID, strExamLevel, listIsPass[i]);
                        DbHelper.GetDatabase().FromSql(strSqlUpdate).SetDbTransaction(trans).ExecuteNonQuery();
                    }
                }
                else
                {
                    string strSql = @"
                            Update TB_Pad_StudentScore_Up     set IsPass='{3}',IsPublish = '1' where AllowExamID = '{0}' and ExamPlaceNO='{1}' and ExamLevel='{2}'  
                            Update TB_Pad_StudentScoreList    set IsPublish = '1' where AllowExamID = '{0}' and ExamPlaceNO='{1}' and ExamLevel='{2}' 
                    ";
                    for (int i = 0; i < listAllowExam.Count; i++)
                    {
                        strSqlUpdate = string.Format(strSql, listAllowExam[i], strExamPlaceID, strExamLevel, listIsPass[i]);
                        DbHelper.GetDatabase().FromSql(strSqlUpdate).SetDbTransaction(trans).ExecuteNonQuery();
                        if (listIsPass[i] == "1")
                        {
                            int ExamLevel_New = (Convert.ToInt32(strExamLevel) + 1);
                            DbHelper.GetDatabase().FromSql("Update TB_Exam_SignUpStudentsSpeciality set ExamLevel='" + ExamLevel_New.ToString() + "',IsPass='0'  where IsActive='1' and AllowExamID = '" + listAllowExam[i] + "'").SetDbTransaction(trans).ExecuteNonQuery();
                        }
                    }
                }
                trans.Commit();
                return string.Empty;
            }
            catch (Exception ex)
            {
                trans.Rollback();
                return ex.Message;
            }
            finally
            {
                trans.Close();
            }
        }
        public TB_Exam_ResultsPublish GetExamResultsPublishModel(string strExamPlaceNO, string strSpecialityKindsNO, string strExamLevel)
        {
            TB_Exam_ResultsPublish model = DbHelper.GetDatabase().From<TB_Exam_ResultsPublish>().Where(TB_Exam_ResultsPublish._.SpecialityKindsNO == strSpecialityKindsNO
                && TB_Exam_ResultsPublish._.ExamPlaceNO == strExamPlaceNO && TB_Exam_ResultsPublish._.ExamLevel == strExamLevel).ToFirst();
            if (model != null)
            {
                model.Attach();
            }
            return model;
        }
        public TB_Exam_SignUpStudentsSpeciality GetExamSignUpStudentsSpecialityModel(string strExamPlaceNO, string strSpecialityKindsNO, 
            string strExamLevel, string strAllowExamID)
        {
            return DbHelper.GetDatabase().From<TB_Exam_SignUpStudentsSpeciality>().Where(TB_Exam_SignUpStudentsSpeciality._.AllowExamID == strAllowExamID
                && TB_Exam_SignUpStudentsSpeciality._.ExamPlaceNO == strExamPlaceNO && TB_Exam_SignUpStudentsSpeciality._.ExamLevel == strExamLevel
                && TB_Exam_SignUpStudentsSpeciality._.SpecialityKindsNO == strSpecialityKindsNO).ToFirst();
        }

        public void SaveResultsPublishData(TB_Exam_ResultsPublish modelPublish, List<TB_Exam_ResultsPublishList> listPublish, string strIsInOut)
        {
            DbTrans trans = DbHelper.GetDatabase().BeginTransaction();
            try
            {
                if (modelPublish != null)
                {
                    if (!modelPublish.IsNewModel)
                    {
                        DbHelper.GetDatabase().Update<TB_Exam_ResultsPublish>(modelPublish, trans);
                    }
                    else
                    {
                        DbHelper.GetDatabase().Insert<TB_Exam_ResultsPublish>(modelPublish, trans);
                    }
                    if (strIsInOut == "2")
                    {
                        DbHelper.GetDatabase().FromSql("Update TB_Pad_StudentScoreList set IsPublish='1' where ExamPlaceNO='" + modelPublish.ExamPlaceNO + "' and SpecialityKindsNO='" + modelPublish.SpecialityKindsNO + "' and ExamLevel='" + modelPublish.ExamLevel + "'").SetDbTransaction(trans).ExecuteNonQuery();
                    }
                    DbHelper.GetDatabase().FromSql("delete TB_Exam_ResultsPublishList where ExamPlaceNO='" + modelPublish.ExamPlaceNO + "' and SpecialityKindsNO='" + modelPublish.SpecialityKindsNO + "' and ExamLevel='" + modelPublish.ExamLevel + "'").SetDbTransaction(trans).ExecuteNonQuery();
                    if (listPublish != null && listPublish.Count > 0)
                    {
                        foreach (TB_Exam_ResultsPublishList model in listPublish)
                        {
                            if (!model.IsNewModel)
                            {
                                DbHelper.GetDatabase().Update<TB_Exam_ResultsPublishList>(model, trans);
                            }
                            else
                            {
                                DbHelper.GetDatabase().Insert<TB_Exam_ResultsPublishList>(model, trans);
                            }
                        }
                    }
                }
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
            }
            finally
            {
                trans.Close();
            }
        }
    }
}
